﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace Aliang.DB.Helper.SQLServer
{

    /// <summary>
    /// 配置获取
    /// </summary>

    public class DBConfig
    {
        public static string getBasicConn()
        {
            return ConfigurationManager.ConnectionStrings["SqlServerDataSource"].ConnectionString;
        }
    }

    /// <summary>
    /// 数据库帮助类
    /// </summary>
    public class SqlDBHelper
    {
        /// <summary>
        /// 链接字符串
        /// </summary>
        private string connectionString;

        public SqlDBHelper()
        {
            connectionString = DBConfig.getBasicConn();
        }

        public SqlDBHelper(string connectionString)
        {
            this.connectionString = connectionString;
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteSqlWithRet(string sql)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = sql;
                    connection.Open();
                    int result = Convert.ToInt32(command.ExecuteScalar());//执行查询，并返回查询所返回的结果集中第一行的第一列。 忽略其他列或行
                    connection.Close();
                    return result;
                }

            }
            catch (Exception)
            {
                throw;
            }
        }


        /// <summary>
        ///  执行SQL语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public int ExecuteSqlWithRet(string sql, params SqlParameter[] pars)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = sql;
                    command.Parameters.AddRange(pars);
                    connection.Open();
                    int result = Convert.ToInt32(command.ExecuteScalar());
                    connection.Close();
                    return result;
                }

            }
            catch (Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteSql(string sql)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = sql;
                    connection.Open();
                    int result = command.ExecuteNonQuery();
                    connection.Close();
                    return result;
                }

            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public int ExecuteSql(string sql, params SqlParameter[] pars)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = sql;
                    command.Parameters.AddRange(pars);
                    connection.Open();
                    int result = command.ExecuteNonQuery();
                    connection.Close();
                    return result;
                }

            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    DataSet ds = new DataSet();
                    SqlDataAdapter sda = new SqlDataAdapter(sql, connection);
                    sda.Fill(ds);

                    if (ds != null && ds.Tables.Count > 0)
                    {
                        return ds.Tables[0];
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql, params SqlParameter[] pars)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    DataSet ds = new DataSet();
                    SqlCommand command = new SqlCommand();
                    command.Connection = connection;
                    command.CommandText = sql;
                    command.Parameters.AddRange(pars);
                    SqlDataAdapter sda = new SqlDataAdapter(command);

                    sda.Fill(ds);

                    if (ds != null && ds.Tables.Count > 0)
                    {
                        return ds.Tables[0];
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public DataSet GetDataTableByPage(int start, int pageSize, string table, string columns, string order, string filter)
        {
            try
            {
                string sql = "SELECT TOP " + pageSize + " " + columns + " FROM (SELECT ROW_NUMBER() OVER (ORDER BY " + order + " ) AS RowNumber," + columns + " FROM " + table + " {0}) A WHERE RowNumber> " + start + " order by RowNumber ";
                sql = string.Format(sql, filter.Trim() == "" ? "" : " WHERE " + filter.Trim());

                //记录总数(非结果集记录总数)
                string sql_count = "SELECT COUNT(*) FROM " + table;
                sql_count += filter.Trim() == "" ? "" : " WHERE " + filter;


                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    DataSet ds = new DataSet();
                    ds.Tables.Add("result");
                    ds.Tables.Add("count");

                    SqlDataAdapter sda = new SqlDataAdapter();
                    sda.SelectCommand = new SqlCommand();
                    sda.SelectCommand.Connection = connection;

                    sda.SelectCommand.CommandText = sql;
                    sda.Fill(ds.Tables["result"]);

                    sda.SelectCommand.CommandText = sql_count;
                    sda.Fill(ds.Tables["count"]);

                    return ds;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        public DataSet GetDataTableByPage(int start, int pageSize, string table, string columns, string order, string filter, params SqlParameter[] pars)
        {
            try
            {
                string sql = "SELECT TOP " + pageSize + " " + columns + " FROM (SELECT ROW_NUMBER() OVER (ORDER BY " + order + " ) AS RowNumber," + columns + " FROM " + table + " {0}) A WHERE RowNumber> " + start + " ORDER BY " + order;
                sql = string.Format(sql, filter.Trim() == "" ? "" : " WHERE " + filter.Trim());

                //记录总数(非结果集记录总数)
                string sql_count = "SELECT COUNT(*) FROM " + table;
                sql_count += filter.Trim() == "" ? "" : " WHERE " + filter;


                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    DataSet ds = new DataSet();
                    ds.Tables.Add("result");
                    ds.Tables.Add("count");

                    SqlDataAdapter sda = new SqlDataAdapter();
                    sda.SelectCommand = new SqlCommand();
                    sda.SelectCommand.Connection = connection;
                    sda.SelectCommand.Parameters.AddRange(pars);

                    sda.SelectCommand.CommandText = sql;
                    sda.Fill(ds.Tables["result"]);

                    sda.SelectCommand.CommandText = sql_count;
                    sda.Fill(ds.Tables["count"]);

                    return ds;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }
    }
}